<?php require('inc/header.php');
$mysqli = dbconnect();
$query = "SELECT COUNT(DISTINCT DATE(date)) FROM uploads";
$statement = $mysqli->stmt_init();
if ($statement->prepare($query))
{
$statement->execute();
$statement->store_result();
$statement->bind_result($date);
$statement->fetch();
$days = $date;
$statement->close();
} else {
echo "We're sorry, but we could not display site statistics at this tme because a database error has occured. Please report the following message to the site administrator: " . $mysqli->error;
$statement->close();
$mysqli->close();
exit;
}

$query2 = "SELECT COUNT(id), SUM(filesize), AVG(filesize), SUM(pageviews), SUM(downloads) FROM uploads";
$statement = $mysqli->stmt_init();
$statement->prepare($query2);
$statement->execute();
$statement->fetch();
$total = dbresult($statement,0,0);
$avgday = intval(dbresult($statement,0,0)/$days);
$size = round(dbresult($statement,0,1)/1024/1024,2);
$avg = round(dbresult($statement,0,2)/1024/1024,2);
$totalv = dbresult($statement,0,3);
$totald = dbresult($statement,0,4);
$statement->close();
?>

<h1>Statistics</h1>
<table>
	<?php echo '<tr><td><b>Total posts:</b></td><td>' . $total . '</td></tr>
	<tr><td><b>Average posts/day:</b></td><td>' . $avgday . '</td></tr>
	<tr><td><b>Total size:</b></td><td>' . $size . ' MB</td></tr>
	<tr><td><b>Average size:</b></td><td>'.$avg.' MB</td></tr>
	<tr><td><b>Total views:</b></td><td>'.$totalv.'</td></tr>
	<tr><td><b>Total downloads:</b></td><td>'.$totald.'</td></tr>';
	?>
</table>
<h3>Dates with most uploads</h3>
<table>
	<tr><th>Date</th><th>Posts</th></tr>
	<?php

	$query3 = "SELECT DATE(date) as short_date, COUNT(id) AS posts FROM uploads GROUP BY short_date ORDER BY posts DESC LIMIT 5";
	$statement = $mysqli->stmt_init();
	$statement->prepare($query3);
$statement->execute();
	while ($row = $statement->fetch())
	{
		echo '<tr><td>'.$row['short_date'].'</td><td>'.$row['posts'].'</td></tr>';
	}
	$statement->close();
	?>
</table>
<h3>Post info</h3>
<form name="grid" action="/stats" method="post">
Show: <select name="limit">
<option value="5">5 posts</option>
<option value="10">10 posts</option>
<option value="15">15 posts</option>
<option value="20" selected="true">20 posts</option>
<option value="30">30 posts</option>
<option value="50">50 posts</option>
<option value="100">100 posts</option>
</select>
Sort by: <select name="sort">
<option value="id" selected="true">ID</option>
<option value="filename">Filename</option>
<option value="date">Date</option>
<option value="filesize">Size</option>
<option value="pageviews">Views</option>
<option value="downloads">Downloads</option>
</select>
Order: <select name="order">
<option value="ASC">Ascending</option>
<option value="DESC" selected="true">Descending</option>
</select>
<p><input type="checkbox" value="" name="show_hosts" unchecked="unchecked" style="width: 40px" />Show Hostnames (breaks layout)</p>
<p><input type="submit" value="Apply" title="Apply" style="width: 100px" /></p>
</form>
<?php 
$limit = intval($_POST['limit']);
if ($limit < 1)
{
	$limit = 20;
}
$sorting = array('id','filename','date','filesize','pageviews','downloads');
$sort = strtolower($_POST['sort']);
if (!in_array($sort, $sorting))
{
	$sort = 'id';
}
$order = strtoupper($_POST['order']);
if ($order != 'ASC' && $order != 'DESC')
{
	$order = 'DESC';
}
$hostnames = isset($_POST['show_hosts']);
$ip = '';
if (isset($_GET['ip']))
{
	$ip = " WHERE ip = '".mysql_real_escape_string($_GET['ip'])."'";
    $hostnames = false;
}
$result = null;
if ($sort == 'id')
{
	$result = $mysqli->query("SELECT * FROM uploads".$ip." ORDER BY ".$sort." ".$order." LIMIT ".$limit);
}
else
{
	$result = $mysqli->query("SELECT * FROM uploads".$ip." ORDER BY ".$sort." ".$order.", id DESC LIMIT ".$limit);
}
require_once('Net/GeoIP.php');
$geoip = Net_GeoIP::getInstance('inc/GeoIP.dat');
$loc = array();
?>
<table>
	<tr><th>#</th><th>Filename</th><th>Date</th><th>IP</th><th>Cntry</th><th>Size MB</th><th>Vws</th><th>Dls</th></tr>
<?php
while ($row = $result->fetch_array())
  {
    if (!isset($loc[$row['ip']]))
    {
      $loc[$row['ip']] = $geoip->lookupCountryCode($row['ip']);
    }
    echo '<tr><td><a href="/'.$row['id'].'">'.$row['id'].'</a></td><td>'.$row['filename'].'</td><td>'.$row['date'].'</td><td><a href="/ap?ip='.$row['ip'].'">'.($hostnames ? gethostbyaddr($row['ip']) : $row['ip']).'</a></td><td>'.$loc[$row['ip']].'</td><td>'.round($row['filesize']/1024/1024,2).'</td><td>'.$row['pageviews'].'</td><td>'.$row['downloads'].'</td></tr>';
  }
$mysqli->close();
?>
	</table>
<?php require('inc/footer.php');
?>